/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.mavenproject1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.h2.tools.Server;

/**
 *
 * @author JSJSYS
 */
public class H2Test {
    //驱动类，H2jar包中可以找到
     static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库
   // static final String JDBC_URL = "jdbc:h2:E:/H23/mydb";
      static final String JDBC_URL = "jdbc:h2:tcp://localhost/C:/Users/JSJSYS/Test";
     // static final String JDBC_URL = "jdbc:h2:tcp://localhost/mem:mydb";
    //用户名
     static final String USER = "sa";
    //密码
     static final String PASSWORD = "sa";
     
     public static void main(String args[]) throws SQLException{
         try {
             
             // 加载驱动
             Class.forName(DRIVER_CLASS);
         } catch (ClassNotFoundException ex) { 
             Logger.getLogger(H2Test.class.getName()).log(Level.SEVERE, null, ex);
         }
          // 根据连接URL，用户名，密码获取数据库连接
        Connection conn = null;
       
         try {
             conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
         } catch (SQLException ex) {
             Logger.getLogger(H2Test.class.getName()).log(Level.SEVERE, null, ex);
         }
        // 创建一个Statement 
        Statement stmt = null;
         try {
             stmt = conn.createStatement();
         } catch (SQLException ex) {
             Logger.getLogger(H2Test.class.getName()).log(Level.SEVERE, null, ex);
         }
         try {
             //如果存在表就先删除
             stmt.execute("DROP TABLE IF EXISTS stu_info");
             //创建stu_info表
                      stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");
             //新增4 条
             stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");
        //新增4 条
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','程凌霄','男','20151101066')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','陈秋云','女','20151101026')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','黍路','女','20151101056')");
        stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','田梦','女','20151101096')");
             p("++++++++++++++++++++++++++++++++++++++显示刚保存的2条数据：");  
             
         } catch (SQLException ex) {
             Logger.getLogger(H2Test.class.getName()).log(Level.SEVERE, null, ex);
         }finally{
             p("end!!!!");
         }
         stmt.close();  
        //关闭连接
        conn.close();
        System.out.println("------jdbc -end!!!------");
         }
     
     public static List<StudentInfo> showAll(StudentService service){
        p("=============================显示目前学生表的所有的记录： ");
        List<StudentInfo> list =  service.queryAll();
        list.forEach((info) -> {
            p(""+info.getId()+"\t"+info.getName()+"\t"+info.getSex()+"\t"+info.getStudentId());
         });
        return list;
    }
         static void p(String str){
             System.out.println(str);
    }

          private static void show( Statement stmt) throws Exception{
        //查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
          while (rs.next()) {
            p("\t" + rs.getString(1) + rs.getString("name") + "\t" + rs.getString("sex")+"\t"+rs.getString(4));
        }
    }
    static Server s=null;
    private static void  initServer(){
        try { 
            s=  Server.createTcpServer().start();
            p(" create H2 server successfully!!!!!!! ");
        } catch (SQLException ex) {
            Logger.getLogger(H2Test.class.getName()).log(Level.SEVERE, null, ex);
            p(" create H2 server failure ");
        }
        
    }
    private static int num =1;
    private static int getId(){
        return num++  ;
    }
         

    private static class StudentInfo {

        public StudentInfo() {
        }

        private String getId() {
            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }

        private String getName() {
            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }

        private String getSex() {
            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }

        private String getStudentId() {
            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }
    }

    private static class StudentService {

        public StudentService() {
        }

        private List<StudentInfo> queryAll() {
            throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        }
    }
}


